package com.siemens.industrial.web.mvc.dao.partner;

import com.siemens.industrial.model.partner.DeviceIdleInfo;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.Types;
import java.util.List;
import java.util.Map;

/**
 * Created by qupen on 2016/8/31.
 */
@Service
public class PartnerDeviceIdleInfoDao {
    @Resource
    JdbcTemplate jdbcTemplate;

    private static final String databaseTag = "{database}";
    private static final String getDeviceIdleInfoQuery = "SELECT * FROM `" + databaseTag + "`.`device_idle_info` WHERE `device_id` = ? AND `start_time` = ?";
    private static final String getDeviceIdleInfoListInDeviceQuery = "SELECT * FROM `" + databaseTag + "`.`device_idle_info` WHERE `device_id` = ?";
    private static final String getDeviceIdleInfoListInTimeQuery = "SELECT * FROM `" + databaseTag + "`.`device_idle_info` WHERE `start_time` = ?";
    private static final String getDeviceIdleInfoListQuery = "SELECT * FROM `" + databaseTag + "`.`device_idle_info`";
    private static final String saveDeviceIdleInfoQuery = "INSERT INTO `" + databaseTag + "`.`device_idle_info` (`device_id`, `start_time`, `end_time`, `idle_reason`, `user_id`) VALUES (?,?,?,?,?)";
    private static final String updateDeviceIdleInfoQuery = "UPDATE `" + databaseTag + "`.`device_idle_info` SET `repair_info`=? WHERE `device_id`=? and`start_time`=?;";
    private static final String getOneReason = "SELECT \n" +
            "    meta_key, meta_definition, user_name\n" +
            "FROM\n" +
            "    (SELECT \n" +
            "        meta_key, meta_definition, user_id\n" +
            "    FROM\n" +
            "        (SELECT \n" +
            "        *\n" +
            "    FROM\n" +
            "        `" + databaseTag + "`.device_idle_info\n" +
            "    WHERE\n" +
            "        device_id = ?\n" +
            "            AND start_time = ?) a\n" +
            "    LEFT JOIN (SELECT \n" +
            "        *\n" +
            "    FROM\n" +
            "        `" + databaseTag + "`.`metadata`) b ON a.idle_reason = b.meta_id) c\n" +
            "        LEFT JOIN\n" +
            "    (SELECT \n" +
            "        user_name, user_id\n" +
            "    FROM\n" +
            "        `" + databaseTag + "`.`user`) d ON c.user_id = d.user_id";

    private static final String getFaultReasonList = "SELECT \n" +
            "    c.device_id,\n" +
            "    c.device_unique_id,\n" +
            "    b.start_time,\n" +
            "    b.end_time,\n" +
            "    b.repair_info,\n" +
            "    a.meta_definition\n" +
            "FROM\n" +
            "    `" + databaseTag + "`.metadata a\n" +
            "        INNER JOIN\n" +
            "    `" + databaseTag + "`.device_idle_info b ON a.meta_id = b.idle_reason\n" +
            "        INNER JOIN\n" +
            "    `" + databaseTag + "`.device c ON b.device_id = c.device_id\n" +
            "WHERE\n" +
            "    a.meta_key = 'FaultType';";

    public static final String getIdleByDeviceIdAndStartTime = "SELECT * FROM `" + databaseTag + "`.device_idle_info where device_id=? and start_time=FROM_UNIXTIME(?,'%Y-%m-%d %H:%i:%s');";

    private static final String getFaultType = "select a.meta_id,a.meta_definition from `"+databaseTag+"`.metadata a where a.meta_key = 'FaultType' AND `delete_time` IS NULL;";

    private static final String getIdleReasons = "select a.meta_id,a.meta_definition from `"+databaseTag+"`.metadata a where a.meta_key = 'IDLE'  AND `delete_time` IS NULL;";

    public DeviceIdleInfo getDeviceIdleInfo(String database, String deviceId, String startTime) {
        try {
            return jdbcTemplate.queryForObject(getDeviceIdleInfoQuery.replace(databaseTag, database), new Object[]{deviceId, startTime}, new int[]{Types.VARCHAR, Types.VARCHAR}, DeviceIdleInfo.getRowMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public DeviceIdleInfo getDeviceIdleInfoByDeviceIdAndStartTime(String database, String deviceId, long startTime) {
        try {
            return jdbcTemplate.queryForObject(getIdleByDeviceIdAndStartTime.replace(databaseTag, database), new Object[]{deviceId, startTime}, DeviceIdleInfo.getRowMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public List<DeviceIdleInfo> getDeviceIdleInfoList(String database) {
        return jdbcTemplate.query(getDeviceIdleInfoListQuery.replace(databaseTag, database), DeviceIdleInfo.getRowMapper());
    }

    public List<DeviceIdleInfo> getDeviceIdleInfoListInTime(String database, java.util.Date startTime) {
        return jdbcTemplate.query(getDeviceIdleInfoListInTimeQuery.replace(databaseTag, database), new Object[]{new java.sql.Date(startTime.getTime())}, DeviceIdleInfo.getRowMapper());
    }

    public List<DeviceIdleInfo> getDeviceIdleInfoListInDevice(String database, String deviceId) {
        return jdbcTemplate.query(getDeviceIdleInfoListInDeviceQuery.replace(databaseTag, database), new Object[]{deviceId}, DeviceIdleInfo.getRowMapper());
    }

    public void updateDeviceIdleInfo(String database, DeviceIdleInfo deviceIdleInfo) {
        jdbcTemplate.update(updateDeviceIdleInfoQuery.replace(databaseTag, database), pss -> {
                    pss.setString(1, deviceIdleInfo.getRepairInfo());
                    pss.setString(2, deviceIdleInfo.getDeviceId());
                    pss.setTimestamp(3, new java.sql.Timestamp(deviceIdleInfo.getStartTime().getTime()));
                }
        );
    }

    public int saveDeviceIdleInfo(String database, Map map, String userId) {
        return jdbcTemplate.update(saveDeviceIdleInfoQuery.replace(databaseTag, database), pss -> {
                    pss.setString(1, map.get("deviceId").toString());
                    pss.setString(2, map.get("startTime").toString());
                    pss.setString(3, map.get("endTime").toString());
                    pss.setString(4, map.get("reasonId").toString());
                    pss.setString(5, userId);
                }
        );
    }

    public Map<String, Object> getOneReason(String database, String deviceId, String startTime) {
        List<Map<String, Object>> list = jdbcTemplate.queryForList(getOneReason.replace(databaseTag, database), deviceId, startTime);
        if (list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    public List<Map<String, Object>> getFaultReasonList(String database) {
        return jdbcTemplate.queryForList(getFaultReasonList.replace(databaseTag, database));
    }

    public List getFaultType(String database) {
        return jdbcTemplate.queryForList(getFaultType.replace(databaseTag, database));
    }

    public List getIdleReasons(String database){
        return jdbcTemplate.queryForList(getIdleReasons.replace(databaseTag,database));
    }
}
